package com.cbd.dao.impl;

import java.math.BigInteger;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;

import com.cbd.dao.OperatorDao;
import com.cbd.pojo.Landlady;
import com.cbd.pojo.Level;
import com.cbd.pojo.Operator;
import com.cbd.pojo.PageBean;
import com.cbd.pojo.User;
@Repository
public class OperatorDaoImpl implements OperatorDao {
	@Resource(name="sessionFactory")
	private SessionFactory sessionFactory;

	@Override
	public int login(User user) {
		// TODO Auto-generated method stub
		return 0;
	}
	
	@Override
	public int addOperatorDao(Operator operator) {
		// TODO Auto-generated method stub
		Session session = sessionFactory.getCurrentSession();
		session.save(operator);
		int id = operator.getOperatorID();
		List<Level> level = operator.getLevels();
		for (Level level2 : level) {
			Query query = session.createSQLQuery("insert into operatorlevel(levelID,operatorID)values(?,?)");
			query.setInteger(0, level2.getLevelID());
			query.setInteger(1, id);
			query.executeUpdate();
		}
		
		return 0;
	}

	@Override
	public int deleteOperator(int operatorId) {
		// TODO Auto-generated method stub
		System.out.println(operatorId);
		Session session=sessionFactory.getCurrentSession();
		Query query1 = session.createQuery("delete from Operator where userID = ?");			
		query1.setInteger(0, operatorId);
		query1.executeUpdate();
		Query query2 = session.createQuery("delete from User where userID = ?");			
		query2.setInteger(0, operatorId);
		int num = query2.executeUpdate();
		return num;
	}

	
	@Override
	public List<Object[]> showPIM(int id) {
		// TODO Auto-generated method stub
		Session session=sessionFactory.getCurrentSession();
		//SELECT u.userName,u.userPWD,o.operatorName,o.operatorTel,l.levelDescribe FROM `user` u LEFT JOIN operator o ON u.userID=o.userID LEFT JOIN operatorlevel ol ON o.operatorID=ol.operatorID LEFT JOIN `level` l ON ol.levelID=l.levelID where u.userID=1;
		Query query=session.createSQLQuery("SELECT u.userName,u.userPWD,o.operatorName,o.operatorTel,l.levelDescribe FROM `user` u LEFT JOIN operator o ON u.userID=o.userID LEFT JOIN operatorlevel ol ON o.operatorID=ol.operatorID LEFT JOIN `level` l ON ol.levelID=l.levelID where u.userID=?;");
		query.setParameter(0, id);
		List<Object[]> list=query.list();
		return list;
	}

	@Override
	public int updatepwd(String pwd,int id) {
		// TODO Auto-generated method stub
		Session session=sessionFactory.getCurrentSession();
		Query query=session.createSQLQuery("update user set userPWD=? where userID=?");
		query.setParameter(0, pwd);
		query.setParameter(1, id);
		int cou=query.executeUpdate();
		return cou;
	}

	@Override
	public int updatetel(String tel,int id) {
		// TODO Auto-generated method stub
		Session session=sessionFactory.getCurrentSession();
		Query query=session.createSQLQuery("update operator set operatorTel=? where userID=?");
		query.setParameter(0, tel);
		query.setParameter(1, id);
		int cou=query.executeUpdate();
		return cou;
	}

	@Override
	public Operator findOperatorBuUserId(int userId) {
		// TODO Auto-generated method stub
		Session session=sessionFactory.getCurrentSession();
		Query query=session.createQuery("from Operator where userID=?");
		query.setParameter(0, userId);
		Operator operator=(Operator) query.list().get(0);
		return operator;
	}

	@Override
	public List<Operator> findAllOperator(PageBean pageBean) {
		// TODO Auto-generated method stub
		Session session = sessionFactory.getCurrentSession();	
		Query query = session.createQuery("from Operator order by operatorID DESC");
		int rowPage=pageBean.getPageSize();
		query.setFirstResult((pageBean.getCurPage()-1)*rowPage);
		query.setMaxResults(rowPage);
		return query.list();
	}

	@Override
	public int getOperatorCount() {
		// TODO Auto-generated method stub
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("select count(*) from operator");
		BigInteger num = (BigInteger) query.list().get(0);
		return num.intValue();
	}

	@Override
	public void saveLevelByOperatorId(List<Integer> level, int id) {
		// TODO Auto-generated method stub
		Session session = sessionFactory.getCurrentSession();
		Query q = session.createSQLQuery("select operatorID from operator where userID = ?");
		q.setInteger(0, id);
		int operatorID = (int) q.list().get(0);
		for (int levelId : level) {
			Query query = session.createSQLQuery("insert into operatorlevel(levelID,operatorID)values(?,?)");
			query.setInteger(0, levelId);
			query.setInteger(1, operatorID);
			query.executeUpdate();
		}
	}

	@Override
	public void deleteLevelByOperatorId(int id) {
		// TODO Auto-generated method stub
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("delete from operatorlevel where operatorID in (select operatorID from operator where userID=?)");
		query.setInteger(0, id);
		query.executeUpdate();
	}
	

}
